const sequelize = require('sequelize');
const config = require('../config.js');

var instance = new sequelize(config.database, config.username, config.password, {
    host: config.host,
    dialect: 'mysql',
    pool: {
        max: 5,
        min: 0,
        idle: 30000
    },
    define: {
        charset: 'utf8',
        dialectOptions: {
            collate: 'utf8_general_ci'
        }
    }
});

var menusTable = instance.define('menus', {
    id: {
        type: sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    name: sequelize.STRING(60),
    parent: {
        type: sequelize.INTEGER,
        default: 0
    },
    logo: sequelize.STRING(255),
    link: sequelize.STRING(60),
    flag: sequelize.INTEGER
}, {
    timestamps: false,
    tableName: 'menu'
});

var articleTable = instance.define('articles', {
    id: {
        type: sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    category: sequelize.INTEGER,
    title: sequelize.STRING(60),
    author: sequelize.STRING(60),
    time: sequelize.DATE,
    readCount: sequelize.INTEGER,
    textType: sequelize.TINYINT,
    text: sequelize.TEXT,
    url: sequelize.STRING(255),
    flag: sequelize.TINYINT,
    keywords: sequelize.STRING(255),
    description: sequelize.STRING(1000)
}, {
    timestamps: false,
    tableName: 'artical'
});

var userTable = instance.define('users', {
    id: {
        type: sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    username: sequelize.STRING(60),
    password: sequelize.STRING(255),
    type: sequelize.INTEGER,
    token: sequelize.STRING(1000),
    email: sequelize.STRING(255),
    lastTime: sequelize.DATE,
    avatar: sequelize.STRING(255),
    nickname: sequelize.STRING(255)
}, {
    timestamps: false,
    tableName: 'user'
});

var commentTable = instance.define('comments', {
    id: {
        type: sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    title: {
        type: sequelize.STRING(255),
        notNull: true
    },
    comment: sequelize.STRING(5000),
    replyUserId: {
        type: sequelize.INTEGER,
    },
    replyId: {
        type: sequelize.INTEGER,
        default: 0
    },
    articleId: {
        type: sequelize.INTEGER,
        notNull: true
    },
    userId: {
        type: sequelize.INTEGER,
        notNull: true
    },
    userNickname: sequelize.STRING(255),
    userAvatar: sequelize.STRING(255),
    isRead: {
        type: sequelize.INTEGER,
        notNull: true
    },
    createdTime: sequelize.DATE,
    flag: sequelize.TINYINT
}, {
    timestamps: false,
    tableName: 'comment'
});

var likeTable = instance.define('likes', {
    id: {
        type: sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    userId: {
        type: sequelize.INTEGER,
        notNull: true
    },
    username: sequelize.STRING(60),
    commentId: {
        type: sequelize.INTEGER,
        notNull: true
    },
    status: sequelize.TINYINT
}, {
    timestamps: false,
    tableName: 'likes'
});

commentTable.hasMany(likeTable, {foreignKey: 'commentId', targetKey: 'id'});

module.exports = {
    //menu
    menu: (params) => menusTable.findAll(params),
    addMenuItem: (params) => menusTable.create(params),
    deleteMenuItem: (options) => menusTable.update({flag: 1}, options),
    //user
    user: (params) => userTable.findAll(params, {raw: true}),
    createUser: (params) => userTable.create(params),
    changeUserPwd: (params, options) => userTable.update(params, options),
    editUserInfo: (params, options) => userTable.update(params, options),
    //article
    list: (params) => articleTable.findAll(params),
    addReadCount: (params) => articleTable.update({readCount: sequelize.literal('`readCount`+1')}, params),
    addArticle: (params) => articleTable.create(params),
    updateArticle: (params, options) => articleTable.update({
        ...params,
        readCount: sequelize.literal('`readCount`+1')
    }, options),
    deleteArticle: (params) => articleTable.update({flag: 1}, params),
    lastArticles: (num) => articleTable.findAll({where: {flag: 0}, limit: num, order: [['id', 'DESC']]}),
    //comment
    comment: (params) => commentTable.findAll({
        ...params,
        include: [{
            model: likeTable,
            where:{commentId:sequelize.col('comments.id')},
            required:false
        }]
    }),
    createComment: (params) => commentTable.create(params),
    deleteComment: (options) => commentTable.update({flag: 1}, options),
    readComment: (params) => commentTable.update({isRead: 1}, params),
    //likes
    likes: (params) => likeTable.findAll(params),
    createLike:(params)=>likeTable.create(params),
    updateLike:(params, options) => likeTable.update(params, options),
};